package com.fann;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class LessonOpenDAO {

	public static void main(String[] args) {

	}
	
	public boolean add(LessonOpen LO) {
		
		boolean addJudge = true;
		SimpleDateFormat nowdate = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = nowdate.format(new java.util.Date());

		if (addJudge) {
			Connection conn = null;
			PreparedStatement pstmt = null;
			try {
				DriverManager.registerDriver(new Driver());
				conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic?useUnicode=true&characterEncoding=UTF-8","root", "");

				String strSql = "INSERT INTO lesson_open"
						+ "(lo_id, lo_name, lo_lecturer, lo_description, lo_objectives, lo_outline, lo_hours, lo_textbooks, lo_Sdate, lo_Edate, lo_cost, up_date) "
						+ "values "
						+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				pstmt = conn.prepareStatement(strSql);
				pstmt.setString(1, LO.lo_id);

				pstmt.setString(1, LO.lo_id);
				pstmt.setString(2, LO.lo_name);
				pstmt.setString(3, LO.lo_lecturer);
				pstmt.setString(4, LO.lo_description);
				pstmt.setString(5, LO.lo_objectives);
				pstmt.setString(6, LO.lo_outline);
				pstmt.setInt(7, LO.lo_hours);
				pstmt.setString(8, LO.lo_textbooks);
				pstmt.setString(9, LO.lo_Sdate);
				pstmt.setString(10, LO.lo_Edate);
				pstmt.setInt(11, LO.lo_cost);

				pstmt.setString(12, time);
//				int rowCount = 
						pstmt.executeUpdate();
//				System.out.println(rowCount);
			} catch (SQLException e) {
				e.printStackTrace();
				addJudge = false;
			} finally {
				try {
					if (conn != null && pstmt != null) {
						pstmt.close();
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return addJudge;
	}

	public int update(LessonOpen LO) {

		int rowCount = 0;
		SimpleDateFormat nowdate = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = nowdate.format(new java.util.Date());
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic?useUnicode=true&characterEncoding=UTF-8","root", "");

			String strSql = "update lesson_open set "
					+ "lo_id=?, lo_name=?, lo_lecturer=?, lo_description=?, lo_objectives=?, lo_outline=?, lo_hours=?, lo_textbooks=?, lo_Sdate=?, lo_Edate=?, lo_cost=?, up_date=? "
					+ "where id=?";
			pstmt = conn.prepareStatement(strSql);
			
			pstmt.setString(1, LO.lo_id);
			pstmt.setString(2, LO.lo_name);
			pstmt.setString(3, LO.lo_lecturer);
			pstmt.setString(4, LO.lo_description);
			pstmt.setString(5, LO.lo_objectives);
			pstmt.setString(6, LO.lo_outline);
			pstmt.setInt(7, LO.lo_hours);
			pstmt.setString(8, LO.lo_textbooks);
			pstmt.setString(9, LO.lo_Sdate);
			pstmt.setString(10, LO.lo_Edate);
			pstmt.setInt(11, LO.lo_cost);
			pstmt.setString(12, time);

			pstmt.setInt(13, LO.id);
			
			rowCount = pstmt.executeUpdate();
			System.out.println(LO.id);
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("AAA");
		}
		return rowCount;
	}

	public int delete(String id) {
		int rowCount = 0;
		try {
			DriverManager.registerDriver(new Driver());
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic", "root", "");

			String sql = "delete from lesson_open where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rowCount = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowCount;
	}

	public List<LessonOpen> getAll() {
		
		List<LessonOpen> LOs = new ArrayList<LessonOpen>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic","root","");
			
			String sql = "select * from lesson_open";
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) {

				int id = rs.getInt("id");
				String lo_id = rs.getString("lo_id");
				String lo_name = rs.getString("lo_name");
				String lo_lecturer = rs.getString("lo_lecturer");
				String lo_description = rs.getString("lo_description");
				String lo_objectives = rs.getString("lo_objectives");
				String lo_outline = rs.getString("lo_outline");
				int lo_hours = rs.getInt("lo_hours");
				String lo_textbooks = rs.getString("lo_textbooks");
				String lo_Sdate = rs.getString("lo_Sdate");
				String lo_Edate = rs.getString("lo_Edate");
				int lo_cost = rs.getInt("lo_cost");
				
				LessonOpen LO = new LessonOpen(id, lo_id, lo_name, lo_lecturer, lo_description, lo_objectives, lo_outline, lo_hours, lo_textbooks, lo_Sdate, lo_Edate, lo_cost);
				LOs.add(LO);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return LOs;
	}

	public LessonOpen getLessonOpenById(String id) {
		LessonOpen LO = null;
		
		//int rowCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic","root","");
			
			String sql = "select * from lesson_open where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				LO = new LessonOpen(
						rs.getInt("id"), 
						rs.getString("lo_id"), 
						rs.getString("lo_name"), 
						rs.getString("lo_lecturer"), 
						rs.getString("lo_description"), 
						rs.getString("lo_objectives"), 
						rs.getString("lo_outline"), 
						rs.getInt("lo_hours"), 
						rs.getString("lo_textbooks"), 
						rs.getString("lo_Sdate"), 
						rs.getString("lo_Edate"), 
						rs.getInt("lo_cost")
					);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return LO;
	}

}
